package de.hsw.facbook.model.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import de.hsw.facbook.model.dao.AccountDAO;
import de.hsw.facbook.model.dao.DAOBase;
import de.hsw.facbook.model.entities.Account;
import de.hsw.facebook.model.rowMapper.AccountRowMapper;

public class AccountDAOImpl extends DAOBase implements AccountDAO {

	private final Log logger = LogFactory.getLog(AccountDAOImpl.class);
	private AccountRowMapper accountRowMapper = new AccountRowMapper();

	@Override
	public boolean createAccount(Account account) {

		boolean result = false;

		String query = "INSERT INTO Account (accountID, email, password, surname, lastname, birthday, city, country, gender) VALUES (?,?,?,?,?,?,?,?,?)";

		try {
			Connection con = openConnection();
			PreparedStatement prepStat = con.prepareStatement(query);
			prepStat.setInt(1, account.getID());
			prepStat.setString(2, account.getEmail());
			prepStat.setString(3, account.getPassword());
			prepStat.setString(4, account.getSurname());
			prepStat.setString(5, account.getLastname());
			prepStat.setDate(5, account.getBirthday());
			prepStat.setString(6, account.getCity());
			prepStat.setString(7, account.getCountry());
			prepStat.setString(8, account.getGender());

			prepStat.execute();
			prepStat.close();
			con.close();

			result = true;
		} catch (SQLException e) {
			logger.error("SQLException occured", e);
			result = false;
		}

		return result;
	}

	@Override
	public boolean updateAccount(Account account) {

		boolean result = false;

		return result;
	}

	@Override
	public Account getByID(int ID) {
		logger.debug("entering getByID");
		Account result = new Account();

		String query = "SELECT  accountID,  EMAIL,  PASSWORD,  SURNAME,  LASTNAME,  BIRTHDAY,  CITY,  COUNTRY,  GENDER FROM ACCOUNT WHERE  accountID = ?";
		String query2 = "SELECT FRIENDSHIPID, AccountID1, AccountID2 FROM Friendship WHERE AccountID1 =?";

		try {
			Connection con = openConnection();
			PreparedStatement prepStat = con.prepareStatement(query);
			prepStat.setInt(1, ID);
			ResultSet rs = prepStat.executeQuery();
			ArrayList<Account> friends = new ArrayList<Account>();
			int friendsCount = 0;
			// Wir erwarten nur ein Ergebnis
			if (rs.next()) {
				PreparedStatement prepStat2 = con.prepareStatement(query2);
				prepStat2.setInt(1, ID);
				ResultSet rs2 = prepStat2.executeQuery();
				while (rs2.next()) {
					PreparedStatement prepStat3 = con.prepareStatement(query);
					prepStat3.setInt(1, rs2.getInt("AccountID1"));
					ResultSet rs3 = prepStat3.executeQuery();
					Account account = new Account();
					if (rs3.next()) {
						account = this.accountRowMapper.mapRow(rs3);
						friends.add(account);
						friendsCount++;
					}
					rs3.close();
					prepStat3.close();
				}
				result = this.accountRowMapper.mapRow(rs);
				result.setFriends(friends);
				logger.debug(friendsCount);
				rs2.close();
				prepStat2.close();
			}
			rs.close();
			prepStat.close();
			con.close();
		} catch (SQLException e) {
			logger.error("SQLException occurd" + e);
		}

		return result;
	}

	@Override
	public Account getByEmail(int email) {
		logger.debug("entering getByEmail");
		Account result = new Account();
		ArrayList<Account> friends = new ArrayList<Account>();
		int friendsCount = 0;

		String query = "SELECT Account.ACCOUNTID,  Account.EMAIL,  Account.PASSWORD,  Account.SURNAME,  Account.LASTNAME,  Account.BIRTHDAY,  Account.CITY,  Account.COUNTRY,  Account.GENDER FROM ACCOUNT WHERE  Account.email = ?";
		String query2 = "SELECT FRIENDSHIPID, AccountID1, AccountID2 FROM Friendship WHERE AccountID1 = ? OR AccountID2= ?";

		try {
			Connection con = openConnection();
			PreparedStatement prepStat = con.prepareStatement(query);
			prepStat.setInt(1, email);
			ResultSet rs = prepStat.executeQuery();
			// Wir erwarten nur ein Ergebnis
			while (rs.next()) {
				result = this.accountRowMapper.mapRow(rs);
				PreparedStatement prepStat2 = con.prepareStatement(query2);
				prepStat2.setInt(1, result.getID());
				ResultSet rs2 = prepStat2.executeQuery();
				while (rs2.next()) {
					PreparedStatement prepStat3 = con.prepareStatement(query);
					prepStat3.setInt(1, rs2.getInt("AccountID1"));
					prepStat3.setInt(2, rs2.getInt("AccountID1"));
					ResultSet rs3 = prepStat2.executeQuery();
					Account account = new Account();
					account = this.accountRowMapper.mapRow(rs3);
					friends.add(account);
					rs3.close();
					prepStat3.close();
				}
				rs2.close();
				prepStat2.close();
			}
			result = this.accountRowMapper.mapRow(rs);
			result.setFriends(friends);
			logger.debug(friendsCount);
			rs.close();
			prepStat.close();
			con.close();
		} catch (SQLException e) {
			logger.error("SQLException occurd" + e);
		}

		return result;
	}

	@Override
	public ArrayList<Account> getAll() {
		logger.debug("entering getAll");
		Account result = new Account();
		ArrayList<Account> accounts = new ArrayList<Account>();
		
		String query = "SELECT  accountID,  EMAIL,  PASSWORD,  SURNAME,  LASTNAME,  BIRTHDAY,  CITY,  COUNTRY,  GENDER FROM ACCOUNT";
	
		try {
			Connection con = openConnection();
			PreparedStatement prepStat = con.prepareStatement(query);
			ResultSet rs = prepStat.executeQuery();
			
			
			while (rs.next()) {
				result = this.accountRowMapper.mapRow(rs);
				accounts.add(result);
			}
			rs.close();
			prepStat.close();
			con.close();
		} catch (SQLException e) {
			logger.error("SQLException occurd" + e);
		}

		return accounts;
	}

	@Override
	public Account getByIDWithoutFriends(int ID) {
		logger.debug("entering getByID");
		Account result = new Account();

		String query = "SELECT  accountID,  EMAIL,  PASSWORD,  SURNAME,  LASTNAME,  BIRTHDAY,  CITY,  COUNTRY,  GENDER FROM ACCOUNT WHERE  accountID = ?";
	
		try {
			Connection con = openConnection();
			PreparedStatement prepStat = con.prepareStatement(query);
			prepStat.setInt(1, ID);
			ResultSet rs = prepStat.executeQuery();
			// Wir erwarten nur ein Ergebnis
			if (rs.next()) {
				result = this.accountRowMapper.mapRow(rs);
			}
			rs.close();
			prepStat.close();
			con.close();
		} catch (SQLException e) {
			logger.error("SQLException occurd" + e);
		}

		return result;
	}

}
